Item of the Month Price Tracking

In [1]:
import pandas as pd
import numpy as np
from item_dimension import ItemDimension
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
%matplotlib inline
itemDimension = ItemDimension()
In [2]:
df_id = pd.read_csv("mr. store.txt").set_index("Year")
df_id = df_id.applymap(itemDimension.get_item_id_by_name)
df_id = df_id.applymap(lambda x: np.nan if x == "unknown" else x)
df_id = df_id.astype(float).astype("Int64")
MONTHS = df_id.columns
df = pd.read_csv("iotm_prices/iotm_prices")
# df["datetime"] = df["datetime"].apply(lambda x: datetime.strptime(x[:10],"%Y-%m-%d"))
df["datetime"] = df["datetime"].apply(pd.to_datetime).dt.date
df = df[df["price"] < np.inf]
k = 0
m=df_id.values
f, axes = plt.subplots(17, 12)
f.set_figwidth(20)
f.set_figheight(40)
for axis, year in zip(axes[:,0], range(2020, 2003, -1)):
    axis.set_ylabel(year)
for axis, month in zip(axes[0,:], MONTHS):
    axis.set_title(month)
axes = axes.reshape(-1)
date_ticks = [datetime(2020, i, 1) for i in range(7, 13)] + [datetime(2021, i, 1) for i in range(1, 3)]
for i in range(len(m)):
    for j in range(len(m[i])):
        item_id = m[i,j]
        data = df[df["item_id"]==item_id]
        if len(data) == 0:
            k += 1
            continue
        axis = axes[k]
        axis.fill_between(data["datetime"], np.log10(data["price"]/1000000))
        axis.set_ylim(0,3)
        axis.set_xlim(df["datetime"].min(), df["datetime"].max())
        axis.set_xticks(date_ticks)
        axis.set_xticklabels(map(lambda x: x.strftime("%B")[0], date_ticks))
        axis.set_yticks(range(4))
        axis.set_yticklabels(["1M", "10M", "100M", "1B"])
        k += 1
c:\python27\lib\site-packages\pandas\plotting\_converter.py:129: FutureWarning: Using an implicitly registered datetime converter for a matplotlib plotting method. The converter was registered by pandas on import. Future versions of pandas will require you to explicitly register matplotlib converters.

To register the converters:
	>>> from pandas.plotting import register_matplotlib_converters
	>>> register_matplotlib_converters()
  warnings.warn(msg, FutureWarning)

Mallbot sales and purchases

In [3]:
import plotly
import pandas as pd
import numpy as np
from item_dimension import ItemDimension
import glob
import os
In [4]:
DIR = "./data/sales_backup/"
data = []
for filepath in os.listdir(DIR):
    data.append(pd.read_csv(DIR+filepath))
sales = pd.concat(data).drop_duplicates()
sales["datetime"] = sales["datetime"].apply(pd.to_datetime)
purchases = pd.read_csv("data/purchases/purchases.csv", header = None)
purchases.columns =  ["item_id", "name", "price", "quantity", "datetime"]
purchases["datetime"] = purchases["datetime"].apply(pd.to_datetime)
purchases["net_meat"] = - purchases["price"] * purchases["quantity"]
sales["net_meat"] = sales["meat"]
sales["net_inventory"] = - sales["quantity"]
purchases["net_inventory"] = purchases["quantity"]
In [5]:
purchases = purchases[["name", "datetime", "net_meat", "net_inventory", "price"]]
sales = sales[["name", "datetime", "net_meat", "net_inventory", "price"]]
sales = sales[sales["name"].isin(purchases["name"])]
In [6]:
transactions = pd.concat([purchases, sales])
transactions = transactions.sort_values("datetime")
transactions["cumulative_net_inventory"] = transactions.groupby("name")["net_inventory"].cumsum()
transactions["cumulative_net_meat"] = transactions.groupby("name")["net_meat"].cumsum()
transactions["name"] = transactions["name"].apply(lambda x: x.lower())
In [7]:
data = []
item_names = set(transactions["name"])

start_date = transactions["datetime"].min().date()
end_date = transactions["datetime"].max().date()

n_days = (end_date - start_date).days

for date in (start_date + timedelta(n) for n in range(n_days)):
    truncated_transactions = transactions[transactions["datetime"] < date]
    if len(truncated_transactions) == 0:
        continue
    gb = truncated_transactions.groupby("name")
    df = pd.DataFrame()
    df["net_meat"] = gb["net_meat"].sum()
    df["net_inventory"] = gb["net_inventory"].sum()
    df["price"] = gb["price"].mean()
#     df["price"] = gb["price"].nth(range(-1, -11, -1)).groupby("name").mean()
    df["net_inventory_value"] = df["net_inventory"] * df["price"] + df["net_meat"]
    df["date"] = date
    df.loc[df.index == "pocket wish", "price"] = 50000
    df = df.reset_index()
    data.append(df)
    
df = pd.concat(data).reset_index(drop = True)
c:\python27\lib\site-packages\ipykernel_launcher.py:10: FutureWarning:

Comparing Series of datetimes with 'datetime.date'.  Currently, the
'datetime.date' is coerced to a datetime. In the future pandas will
not coerce, and a TypeError will be raised. To retain the current
behavior, convert the 'datetime.date' to a datetime with
'pd.Timestamp'.

In [8]:
import plotly
import plotly.express as px
fig = px.area(
    df.sort_values("name"),
    x="date",
    y="net_inventory_value",
    color = "name",
    line_group="name",
    labels= {
        "net_inventory_value": "net value"
    },

    title= "Mallbot profit",
    width = 1600,
    height = 900,
    line_shape = "spline"
)
fig.update_layout(legend_traceorder="normal")
fig.update_traces(patch = {"visible": "legendonly"})
fig.update_traces(patch = {"visible": True}, selector = {"name":"mr. accessory"})
fig.show()
fig.write_html("interactive_plot.html")
In [ ]: